<?php
// +----------------------------------------------------------------------
// | 数据库操作类 单例模式(三私一共)
// +----------------------------------------------------------------------
// | Copyright (c) 2019 http://qwn.the2016.com All rights reserved.
// +----------------------------------------------------------------------
// | Author: wm
// +----------------------------------------------------------------------

namespace helper\pattern;

class Singleton
{
    /**
     * 数据库配置
     * @var array
     */
    private static $config = [
        'host'     => '',// 服务器地址
        'port'     => '',// 端口
        'database' => '',// 数据库名
        'username' => '',// 用户名
        'password' => '',// 密码
        'charset'  => 'utf8',// 数据库编码默认采用utf8
        'prefix'   => '',// 数据库表前缀
    ];

    /**
     * 当前数据表名称
     * @var string
     */
    private $table;

    /**
     * 当前数据表前缀
     * @var string
     */
    protected $prefix = '';

    /**
     * 当前查询参数
     * @var array
     */
    protected $options = [
        'field'  => '*', // 字段
        'where'  => '',  // 查询
        'order'  => '',  // 排序
        'group'  => '',  // 分组
        'having' => '',  // 筛选
        'limit'  => '',  // 条数
        'alias'  => '',  // 别名
        'join'   => ''   // 关联
    ];

    /**
     * sql
     * @var string
     */
    private $sql;

    /**
     * 数据表信息
     * @var array
     */
    private static $info;

    /**
     * 当前数据表主键
     * @var string|array
     */
    protected $pk;

    /**
     * 返回或者影响记录数
     * @var int
     */
    protected $numRows = 0;

    /**
     * 错误信息
     * @var string
     */
    protected $error = '';

    /**
     * 存放数据库连接对象
     * @var \mysqli
     */
    private static $link;

    /**
     * 存放实例化的对象
     * @var object
     */
    private static $instance;

    /**
     * 二私有的构造函数 防止在类的外部实例化对象
     * @param $config array 连接数据库的参数
     */
    private function __construct($config)
    {
        $this->setConfig($config);
        $this->connect();// 连接服务器
        $this->setCharset();// 设置字符集
        $this->selectDB();// 连接数据库
    }

    public function test()
    {
        $user = 'root';
        $password = ''; //To be completed if you have set a password to root
        $database = ''; //To be completed to connect to a database. The database must exist.
        $port = NULL; //Default must be NULL to use default port
        $mysqli = new mysqli('127.0.0.1', $user, $password, $database, $port);

        if ($mysqli->connect_error) {
            die('Connect Error (' . $mysqli->connect_errno . ') '
                . $mysqli->connect_error);
        }
        echo '<p>Connection OK '. $mysqli->host_info.'</p>';
        echo '<p>Server '.$mysqli->server_info.'</p>';
        $mysqli->close();
    }

    /**
     * 析构方法
     * @access public
     */
    public function __destruct()
    {
        $this->close();
    }

    /**
     * 获取数据库配置
     * @access public
     * @param null $config
     */
    private function setConfig($config = null)
    {
        //初始化连接数据库参数
        $config['host'] = $config['host'] ?? 'localhost';//主机IP
        $config['port'] = $config['port'] ?? '3306';//端口号
        $config['user'] = $config['user'] ?? 'root';//用户名
        $config['password'] = $config['password'] ?? 'root';//密码
        $config['charset'] = $config['charset'] ?? 'utf8';//字符编码
        $config['database'] = $config['database'] ?? '';//数据库名
        $config['prefix'] = $config['prefix'] ?? '';//表前缀
        self::$config = $config;
    }

    /**
     * 获取数据库配置
     * @access public
     * @return mixed
     */
    private function getConfig()
    {
        return self::$config;
    }

    /**
     * 三私有的__clone
     * 用来阻止是类的外部克隆对象
     */
    private function __clone()
    {

    }

    /**
     * 获取MySQLDB类的实例
     * @param array $config
     * @return object MySQLDB的实例
     */
    public static function getInstance($config = [])
    {
        if (!self::$instance instanceof self){
            self::$instance = new self($config);
        }
        return self::$instance;
    }

    /**
     * 连接服务器
     */
    private function connect()
    {
        $config = $this->getConfig();
        // 创建数据库连接对象实例
        self::$link = @mysqli_connect("{$config['host']}:{$config['port']}",$config['user'],$config['password']) or die('数据库连接失败');
    }

    /**
     * 断开服务器
     */
    private function close()
    {
        mysqli_close(self::$link);
    }

    /**
     * 设置字符集
     * @access private
     */
    private function setCharset()
    {
        $config = $this->getConfig();
        $sql = "SET NAMES '{$config['charset']}'";
        self::query($sql);
    }

    /**
     * 选择数据库
     * @access private
     */
    private function selectDB()
    {
        $config = $this->getConfig();
        $sql = "USE `{$config['database']}`";
        self::query($sql);
    }

    /**
     * SQL查询
     * 如果是数据查询语句，成功返回结果集，失败返回false
     * 如果是数据操作语句，成功返回true,失败返回false;
     * @access public
     * @param $sql string 查询sql语句
     * @return bool|\mysqli_result $result，成功返回资源，失败则输出错误信息，并退出
     */
    public function query($sql)
    {
        $this->sql = $sql;// 记录SQL语句
        if (!$result = mysqli_query(self::$link,$sql)) {
            $this->setError([
                'code' => mysqli_errno(self::$link),
                'message' => mysqli_error(self::$link)
            ]);
            return false;
        }
        return $result;
    }

    /**
     * SQL执行语句 更新与写入
     * @access public
     * @param $sql string 查询sql语句
     * @return bool|\mysqli_result $result，成功返回资源，失败则输出错误信息，并退出
     */
    public function execute($sql)
    {
        $this->sql = $sql;// 记录SQL语句
        if (!$result = mysqli_query(self::$link,$sql)) {
            $this->setError([
                'code' => mysqli_errno(self::$link),
                'message' => mysqli_error(self::$link)
            ]);
            return false;
        }
        return $result;
    }

    /**
     * 指定数据表（不含前缀）
     * @param string $name
     * @return Singleton
     */
    public function name($name)
    {
        $this->table = self::$config['prefix'].$name;
        return $this;// 链式调用,返回当前对象
    }

    /**
     * 指定数据表（含前缀）
     * @param string $table
     * @return Singleton
     */
    public function table($table)
    {
        $this->table = $table;
        return $this;
    }

    /**
     * 事务操作 手动控制事务
     * 启动事务
     */
    public function startTrans()
    {
        //self::query("START TRANSACTION");
        //return self::query("BEGIN");
        return mysqli_begin_transaction(self::$link);
    }

    /**
     * 提交事务
     */
    public function commit()
    {
        //$this->query("COMMIT");
        //return $this->query("END");
        return mysqli_commit(self::$link);
    }

    /**
     * 回滚事务
     *
     */
    public function rollback()
    {
        //$this->query("ROLLBACK");
        //return $this->query("END");
        return mysqli_rollback(self::$link);
    }

    /**
     * 指定查询字段
     * @param string $field
     * @return Singleton
     */
    public function field($field)
    {
        if (!empty($field)) {
            $fieldStr = $this->parseField($field);
        } else {
            $fieldStr = '*';
        }
        $this->options['field'] = $fieldStr;
        return $this;
    }

    /**
     * 查询条件
     * @param mixed $field 字段名
     * @param null $op 表达式
     * @param mixed $condition 查询条件 [1,5,8] 1,5,8
     * @return Singleton
     */
    public function where($field, $op = null, $condition = null)
    {
        if ($op||$op!==null) {
            if ($condition!=null){
                $op_arr = ['in','IN','not in','NOT IN'];
                if (in_array($op,$op_arr)) {
                    if (is_array($condition)) {
                        $condition = implode(',', array_map(function ($value) {
                            return "'" . $value . "'";
                        }, $condition));
                    }
                    $where = " WHERE ".$field.' '.strtoupper($op)." (".$condition.")";
                }else{
                    $where = " WHERE ".$field.' '.$op." '".$condition."'";
                }
            } else {
                $where = " WHERE ".$field." = '".$op."'";
            }
        } else {
            $where = " WHERE ";
            if (count($field) == count($field,1)) {
                //关联数组
                $arrName = array_keys($field);
                $arrValue = array_values($field);
                for ($i=0;$i<count($arrName);$i++) {
                    if ($i==count($arrName)-1) {
                        $where.=$arrName[$i].' = '."'".$arrValue[$i]."'";
                    } else {
                        $where.=$arrName[$i].' = '."'".$arrValue[$i]."' AND ";
                    }
                }
            } else {
                //索引数字
                foreach ($field as $k=>$v) {
                    if ($k==count($field)-1) {
                        $where.=$v[0].$v[1]."'".$v[2]."'";
                    } else {
                        $where.=$v[0].$v[1]."'".$v[2]."' AND ";
                    }
                }
            }
        }
        $this->options['where'] = $this->options['where'] ? $this->options['where'].' AND ' .str_replace("WHERE","",$where) : $where;
        return $this;
    }

    /**
     * 表达式查询
     * @param string $raw 'type=1 AND status=1'
     * @return Singleton
     */
    public function whereRaw($raw)
    {
        $this->options['where'] = ' WHERE '.$raw;
        return $this;
    }

    /**
     * 时间查询
     * @param string $field 'birthday'
     * @param string $exp '>='
     * @param string $condition '1970-10-1'
     * @return Singleton
     */
    public function whereTime($field, $exp, $condition)
    {
        $this->options['where'] = $this->options['where'] ? $this->options['where']." AND ".$field.$exp."'".$condition."'" : ' WHERE '.$field.$exp."'".$condition."'";
        return $this;
    }

    /**
     * 查询ORDER
     * @param string $field 字段
     * @param string $order asc,desc
     * @return Singleton
     */
    public function order($field, $order='asc')
    {
        $order = $order=='asc' ? $order : 'desc';
        $this->options['order'] = " ORDER BY `".$field."` ".$order;
        return $this;
    }

    /**
     * 分组
     * @param string $field 'name'/'name,age'
     * @return Singleton
     */
    public function group($field)
    {
        $this->options['group'] = " GROUP BY ".$field;
        return $this;
    }

    /**
     * 筛选
     * @param string $condition count(test_time)>3
     * @return Singleton
     */
    public function having($condition)
    {
        $this->options['having'] = " HAVING ".$condition;
        return $this;
    }

    /**
     * 查询LIMIT
     * @param int $offset
     * @param int $length
     * @return Singleton
     */
    public function limit($offset, $length = null)
    {
        $this->options['limit'] = ' LIMIT '.$offset . ($length ? ',' . $length : '');
        return $this;
    }

    /**
     * 别名
     * @param $alia
     * @return Singleton
     */
    public function alias($alia)
    {
        $this->options['alias'] = ' AS '.$alia.' ';
        return $this;
    }

    /**
     * INNER JOIN查询 可省略JOIN
     * @param mixed $join
     * @param mixed $condition
     * @return Singleton
     */
    public function join($join, $condition)
    {
        $join = "INNER JOIN ".self::$config['prefix'].$join." ON ".$condition;
        $this->options['join'] = $this->options['join'] ? $this->options['join'].' '.$join : $join;
        return $this;
    }

    /**
     * 关联查询 LEFT
     * @param string $tableAndAs
     * @param string $condition
     * @return Singleton
     */
    public function leftJoin($tableAndAs, $condition)
    {
        $join = "LEFT JOIN ".self::$config['prefix'].$tableAndAs." ON ".$condition;
        $this->options['join'] = $this->options['join'] ? $this->options['join'].' '.$join : $join;
        return $this;
    }

    /**
     * 关联查询 RIGHT
     * @param string $tableAndAs
     * @param string $condition
     * @return Singleton
     */
    public function rightJoin($tableAndAs, $condition)
    {
        $join = "RIGHT JOIN ".self::$config['prefix'].$tableAndAs." ON ".$condition;
        $this->options['join'] = $this->options['join'] ? $this->options['join'].' '.$join : $join;
        return $this;
    }

    /**
     * 关联查询 FULL
     * @param string $tableAndAs
     * @param string $condition
     * @return Singleton
     */
    public function fullJoin($tableAndAs, $condition)
    {
        $join = "FULL JOIN ".self::$config['prefix'].$tableAndAs." ON ".$condition;
        $this->options['join'] = $this->options['join'] ? $this->options['join'].' '.$join : $join;
        return $this;
    }

    /**
     * 查询单个记录
     * @param string $id
     * @return array|bool
     */
    public function find($id = '')
    {
        $this->options['where'] = $id ? " WHERE id = $id" : $this->options['where'];
        $sql = "SELECT ".$this->options['field']." FROM ".$this->table.$this->options['where'];
        return $this->fetchRow($sql);
    }

    /**
     * 查询多个记录
     * @return array
     */
    public function select()
    {
        $sql = "SELECT ".$this->options['field']." FROM ".$this->table.$this->options['alias'].$this->options['join'].$this->options['where'].$this->options['order'].$this->options['limit'].$this->options['group'].$this->options['having'];
        return $this->fetchAll($sql);
    }

    /**
     * 查询某个字段的值
     * @param string $field
     * @return bool|string
     */
    public function value($field)
    {
        $sql = "SELECT ".$field." FROM ".$this->table.$this->options['where'];
        return $this->fetchOne($sql);
    }

    /**
     * 查询某一列的值
     * @param string $field 字段名 多个字段用逗号分隔
     * @param string $key   索引
     * @return array
     */
    public function column($field, $key= '')
    {
        $fieldStr = $key ? "$field,$key" : $field;
        $sql = "SELECT ".$fieldStr." FROM ".$this->table.$this->options['where'].$this->options['limit'];
        $result = $this->query($sql);
        $list = [];
        while ($row = mysqli_fetch_assoc($result)) {
            if ($key) {
                $list[$row[$key]] = $row[$field];
            } else {
                $list[] = $row[$field];
            }
        }
        return $list;
    }

    /**
     * 插入一条记录
     * Db::name('user')->insert($data);
     * @param array $data
     * @param bool $getLastInsID
     * @return bool
     */
    public function insert(array $data, $getLastInsID = false)
    {
        $data = $this->parseData($data);
        return $data;
        $sql = "INSERT INTO ".$this->table." (".implode(',',array_keys($data)).") VALUES ('".implode("','",array_values($data))."')";
        $result = $this->query($sql);
        return $getLastInsID ? $this->getLastInsID() : $result;
    }

    /**
     * field分析
     * @access protected
     * @param  mixed $fields 字段名
     * @return string
     */
    protected function parseField($fields)
    {
        if ('*' == $fields || empty($fields)) {
            $fieldsStr = '*';
        } else {
            $columns = $this->getTableInfo('fields');
            $fields = explode(',',$fields);
            foreach ($fields as $k=>$v) {
                $fields[$k] = trim($v);
                if (!in_array(trim($v),$columns)) {
                    unset($fields[$k]);
                    continue;
                }
            }
            $fieldsStr = implode(',', $fields);
        }
        return $fieldsStr;
    }


    /**
     * field分析
     * @param array $data
     * @return mixed
     */
    private function parseData(array $data)
    {
        $columns = $this->getTableInfo('fields');
        foreach ($data as $key=>$val) {
            // 删除数据表中不存在的字段
            if (!in_array($key,$columns)) {
                unset($data[$key]);
                continue;
            }
            // 删除类型不对的字段
        }
        return $data;
    }

    /**
     * 插入一条记录 存在会先删除后插入
     * Db::name('user')->replace($data);
     * @param array $data
     * @param bool $getLastInsID
     * @return bool
     */
    public function replace($data, $getLastInsID = false)
    {
        $data = $this->parseData($data);
        $sql = "REPLACE INTO ".$this->table."(".implode(',',array_keys($data)).") VALUES ('".implode("','",array_values($data))."')";
        $result = $this->query($sql);
        return $getLastInsID ? $this->getLastInsID() : $result;
    }

    /**
     * 插入多条记录 1：循环插入,2.拼接一条sql,3.使用存储过程,4.使用MYSQL load data local 导入文件
     * insert into table_name (field1_name, field2_name)
     * values (field1_value, field2_name) , (field1_value, field2_name)
     * @author wm
     * @param array $data 待添加的数据，二维数组格式
     * @return string
     */
    public function insertAll($data)
    {
        $fields = [];
        $str = '';
        $columns = $this->getTableInfo('fields');
        foreach ($data as $k=>$v) {
            $str .= '(';
            $i = 0;
            foreach ($v as $key=>$val) {
                // 删除数据表中不存在的字段
                if (!in_array($key,$columns)) {
                    unset($data[$k][$key]);
                    continue;
                }
                $fields[$i] = '`'.$key.'`';
                $str .= "'".addslashes($val)."',";
                $i++;
            }
            $str = substr($str,0,-1);
            $str .= '),';
        }
        $filed_str = implode(',',$fields);
        $val_str = substr($str,0,-1);
        $sql = "INSERT INTO `".$this->table."` ({$filed_str}) VALUES {$val_str}";
        return $this->query($sql);
    }

    /**
     * 批量插入
     * @param array $data 要插入的数据
     * @param int   $each 每次插入的条数
     * @return mixed
     */
    public function batchInsert($data,$each)
    {
        $num = count($data);        // 数据总数
        $step = ceil($num/$each);  // insert执行总次数

        $j = 1;
        $s = $step;

        foreach ($data as $val) {    // 目前仅仅是起到循环作用
            if ($j > $step) break;
            $arr2 = array_slice($data, ($step - $s) * $each, $each);     // 每次取100条
            $sql = "insert into batch VALUES ";
            foreach($arr2 as $v) {
                $exist = $GLOBALS['mysqli']->query("select * from batch where number = '$v' limit 1 "); // 检查要插入的数据是否已存在
                $row = $exist->fetch_array(MYSQLI_ASSOC);
                if(empty($row)){
                    $sql .= "(null,$v,now()),";     // 将sql语句拼接起来
                }
            }
            $sql = rtrim($sql, ',');
            $result = $GLOBALS['mysqli']->query($sql);
            echo $sql,'<br>';
            $j++;
            $s--;
         }
         return $result;
    }

    /**
     * 更新记录
     * @param array $data
     * @return string
     */
    public function update($data)
    {
        $data = $this->parseData($data);
        $pk = $this->getPk();
        if (empty($this->options['where'])) {
            // 如果存在主键数据 则自动作为更新条件
            if (is_string($pk) && isset($data[$pk])) {
                $this->options['where'] = " WHERE $pk=$data[$pk]";
            } else {
                // 如果没有任何更新条件则不执行
                return false;
            }
        }
        unset($data[$pk]);
        $updateStr = '';
        if (!empty($data)) {
            foreach ($data as $k=>$v) {
                $updateStr.= $k."='".$v."',";
            }
            $updateStr = 'SET '.rtrim($updateStr,',');
        }
        $sql = 'UPDATE '.$this->table.' '.$updateStr.$this->options['where'];
        return $this->query($sql);
    }

    /**
     * 删除记录
     * @param integer $id
     * @return bool|resource
     */
    public function delete($id = 0)
    {
        if (empty($this->options['where'])) {
            // 如果存在主键数据 则自动作为更新条件
            if (is_int($id)) {
                $this->options['where'] = " WHERE id = $id";
            } else {
                // 如果没有任何更新条件则不执行
                return false;
            }
        }
        $sql = "DELETE FROM ".$this->table.$this->options['where'];
        return $this->query($sql);
    }

    /**
     * 字段加
     * @param $field
     * @param int $value
     * @return bool
     */
    public function inc($field, $value = 1)
    {
        $sql = "UPDATE `".$this->table."` SET `".$field."` = `".$field."` + ".$value.$this->options['where'];
        return $this->query($sql);
    }

    /**
     * 字段减
     * @param $field
     * @param int $value
     * @return bool
     */
    public function dec($field, $value = 1)
    {
        $sql = "UPDATE `".$this->table."` SET `".$field."` = `".$field."` - ".$value.$this->options['where'];
        return $this->query($sql);
    }

    /**
     * 批量更新
     * update table set
     * column1 = case  when column2 = 1 then 2 else 3 end ,
     * column3 = case  when column4 = 1 then 2 else 3 end
     * where id in (1,2,3,4)
     * @param array $data 待更新的数据，二维数组格式
     * @return bool|string
     * @author wm
     */
    public function updateAll($data)
    {
        $field = $this->options['field'];
        if (!is_array($data) || !$field) {
            return false;
        }

        //将二维数组转换成CASE WHEN THEN的批量更新条件
        $sql = '';
        $keys = array_keys(current($data));
        foreach ($keys as $column) {
            $sql .= sprintf("`%s` = CASE `%s` \n", $column, $field);
            foreach ($data as $line) {
                $sql .= sprintf("WHEN '%s' THEN '%s' \n", $line[$field], $line[$column]);
            }
            $sql .= "END,";
        }
        $updates = rtrim($sql, ',');

        // 获取所有键名为$field列的值，值两边加上单引号，保存在$fields数组中
        $fields = array_column($data, $field);
        $fields = implode(',', array_map(function($value) {
            return "'".$value."'";
        }, $fields));
        $sql = sprintf("UPDATE `%s` SET %s WHERE `%s` IN (%s) %s", $this->table, $updates, $field, $fields, $this->options['where']);
        return $this->query($sql);
    }

    /**
     * 聚合查询
     * @access public
     * @param  string    $aggregate    聚合方法
     * @param  string    $field        字段名
     * @param  bool      $force        强制转为数字类型
     * @return mixed
     */
    private function aggregate($aggregate, $field, $force = false)
    {
        $sql = "SELECT $aggregate(".$field.") AS db_". strtolower($aggregate). "FROM ".$this->table.$this->options['where']." LIMIT 1";
        $result = $this->fetchOne($sql);
        if ($force) {
            $result = (float) $result;
        }
        return $result;
    }

    /**
     * COUNT查询
     * @param $field
     * @return int
     */
    public function count($field = '')
    {
        $field = $field ? $field : '*';
        $count = $this->aggregate('COUNT', $field, true);
        return is_string($count) ? $count : (int) $count;
    }

    /**
     * MAX查询
     * @access public
     * @param  string $field    字段名
     * @param  bool   $force    强制转为数字类型
     * @return mixed
     */
    public function max($field, $force = true)
    {
        return $this->aggregate('MAX', $field, $force);
    }

    /**
     * MIN查询
     * @access public
     * @param  string $field    字段名
     * @param  bool   $force    强制转为数字类型
     * @return mixed
     */
    public function min($field, $force = true)
    {
        return $this->aggregate('MIN', $field, $force);
    }

    /**
     * AVG查询
     * @access public
     * @param  string $field 字段名
     * @return float
     */
    public function avg($field)
    {
        return $this->aggregate('AVG', $field, true);
    }

    /**
     * SUM查询
     * @access public
     * @param  string $field 字段名
     * @return float
     */
    public function sum($field)
    {
        return $this->aggregate('SUM', $field, true);
    }

    /**
     * 清空表
     * @return bool|\mysqli_result
     */
    public function truncate()
    {
        return $this->query("TRUNCATE TABLE {$this->table}");
    }

    /**
     * 从结果集中取得第一行
     * @access private
     * @param $sql string 查询的sql语句
     * @return bool|string int,str(单一的值)
     */
    private function fetchOne($sql)
    {
        $result = $this->query($sql);
        $row = mysqli_fetch_row($result);
        return $row ? $row[0] : false;
    }

    /**
     * 获取一条记录
     * @access private
     * @param $sql string 查询的sql语句
     * @return array|bool 一维数组
     */
    private function fetchRow($sql)
    {
        $result = $this->query($sql);
        if ($result) {
            return mysqli_fetch_array($result,MYSQLI_ASSOC);//mysqli_fetch_assoc
        } else {
            return false;
        }
    }

    /**
     * 查询多条记录
     * @access private
     * @param $sql string 执行的sql语句
     * @return array 二维数组
     */
    private function fetchAll($sql)
    {
        $result = $this->query($sql);
        $list = [];
        while ($row = mysqli_fetch_assoc($result)) {
            $list[] = $row;
        }
        return $list;
    }

    /**
     * 获取自增ID
     * @return int
     */
    private function getLastInsID()
    {
        return mysqli_insert_id(self::$link);
    }

    /**
     * 获取最后一条SQL
     * @return int
     */
    private function getLastSql()
    {
        return $this->sql;
    }

    /**
     * 获得记录数目
     * @param $result
     * @return int
     */
    private function getNumRows($result)
    {
        return mysqli_num_rows($result);
    }

    /**
     * 获取受影响行数
     * @return int
     */
    private function getAffectedRows()
    {
        return mysqli_affected_rows(self::$link);
    }

    /**
     * 取得数据表的字段信息
     * @access public
     * @param string $schema
     * @return array
     */
    public function getFields($schema = '')
    {
        //$sql = "SELECT COLUMN_NAME,DATA_TYPE,COLUMN_KEY FROM information_schema.columns WHERE table_name='{$this->table}'";
        $schema = $schema ? $schema : $this->table;
        $sql = 'SHOW COLUMNS FROM ' . $schema;
        $result = $this->fetchAll($sql);
        $info = [];
        foreach ($result as $key => $val) {
            $val = array_change_key_case($val);
            $info[$val['field']] = [
                'name' => $val['field'],
                'type' => $val['type'],
                'notnull' => (bool)('' === $val['null']), // not null is empty, null is yes
                'default' => $val['default'],
                'primary' => (strtolower($val['key']) == 'pri'),
                'autoinc' => (strtolower($val['extra']) == 'auto_increment'),
            ];
        }
        return $info;
    }

    /**
     * 取得数据库的表信息
     * @access public
     * @return array
     */
    public function getTables()
    {
        $sql    = 'SHOW TABLES FROM ' . self::$config['database'];
        $result = $this->fetchAll($sql);
        $info   = [];
        foreach ($result as $key => $val) {
            $info[$key] = current($val);
        }
        return $info;
    }

    /**
     * 获取数据表信息
     * @access public
     * @param  mixed  $tableName 数据表名 留空自动获取
     * @param string  $fetch     获取信息类型 包括 fields type bind pk
     * @return array|bool|string
     */
    public function getTableInfo($fetch = '',$tableName = '')
    {
        $schema = $tableName ? $tableName : $this->table;
        if (!isset(self::$info[$schema])) {
            $info = $this->getFields($schema);
            $fields = array_keys($info);
            $bind   = $type   = [];
            // 记录字段类型
            foreach ($info as $key => $val) {
                $type[$key] = $val['type'];
                $bind[$key] = $this->getFieldBindType($val['type']);
                if (!empty($val['primary'])) {
                    $pk[] = $key;
                }
            }
            // 设置主键
            if (isset($pk)) {
                $pk = count($pk) > 1 ? $pk : $pk[0];
            } else {
                $pk = null;
            }
            self::$info[$schema] = ['fields' => $fields, 'type' => $type, 'bind' => $bind, 'pk' => $pk];
        }
        return $fetch ? self::$info[$schema][$fetch] : self::$info[$schema];
    }

    /**
     * 获取数据表主键
     */
    public function getPK()
    {
        //"SELECT column_name FROM INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` WHERE table_name='表名' AND constraint_name='PRIMARY'";
        if (!empty($this->pk)) {
            $pk = $this->pk;
        } else {
            $pk = $this->getTableInfo('pk');
        }
        return $pk;
    }

    /**
     * 获取字段类型
     * @access public
     * @param  string $type 字段类型
     * @return string
     */
    private function getFieldBindType($type)
    {
        if (0 === strpos($type, 'set') || 0 === strpos($type, 'enum')) {
            $bind = 'STR';
        } elseif (preg_match('/(double|float|decimal|real|numeric)/is', $type)) {
            $bind = 'FLOAT';
        } elseif (preg_match('/(int|serial|bit)/is', $type)) {
            $bind = 'INT';
        } elseif (preg_match('/bool/is', $type)) {
            $bind = 'BOOL';
        } else {
            $bind = 'STR';
        }
        return $bind;
    }

    /**
     * 设置异常
     * @param array $data
     */
    private function setError($data)
    {
        $error = 'SQL语句执行失败\n';
        $error .= '[ 错误编号 ] : '.$data['code'].'\n';
        $error .= '[ 错误信息 ] : '.$data['message'].'\n';
        $this->error = $error;
    }

    /**
     * 获取异常
     * @access public
     * @return string
     */
    public function getError()
    {
        $error = $this->error;
        if (!$this->sql) {
            $error .= "[ SQL语句 ] : " . $this->getLastsql();
        }
        return $error;
    }
}
